package com.lcifn.utils;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * Title: ExcelUtil.java<br>
 * Description: Excel导入导出工具类<br>
 * 
 * @author lichao 
 */
public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 输入流解析excel
     * 
     * @author lichao 2015年12月2日
     * @param is
     *            excel输入流
     * @param skipRow
     *            跳过行数
     * @return sheet和
     */
    public static Map<Integer, List<String[]>> parseExcelWithJxl(InputStream is, int skipRow) {
        try {
            Map<Integer, List<String[]>> sheetMap = new HashMap<Integer, List<String[]>>();
            // 创建workbook
            Workbook workbook = Workbook.getWorkbook(is);
            // 获取sheet
            Sheet[] sheets = workbook.getSheets();
            // 解析sheet
            for (int i = 0; i < sheets.length; i++) {
                Sheet sheet = sheets[i];
                List<String[]> rowList = new ArrayList<String[]>();
                // 解析行
                for (int j = Math.max(skipRow, 0); j < sheet.getRows(); j++) {
                    Cell[] cells = sheet.getRow(j);
                    String[] cellContents = new String[cells.length];
                    // 解析cell
                    for (int k = 0; k < cells.length; k++) {
                        cellContents[k] = cells[k].getContents();
                    }
                    rowList.add(cellContents);
                }
                sheetMap.put(i, rowList);
            }
            return sheetMap;
        } catch (Exception e) {
            logger.error("", e);
        }
        return null;
    }

    /**
     * excel导出
     * 
     * @author lichao 2015年11月30日
     * @param clazz
     *            实体Class
     * @param data
     *            导出数据
     * @param excelBeanList
     *            实体属性名和中文名组装的ExcelBean List
     * @param os
     *            输出流
     */
    public static <T> void exportExcelWithJxl(Class<T> clazz, List<T> data, List<ExcelBean> excelBeanList, OutputStream os) {
        try {
            // 创建workbook
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            // 创建sheet
            WritableSheet sheet = workbook.createSheet("sheet1", 0);
            // 填充表头
            int index = 0;
            for (ExcelBean bean : excelBeanList) {
                Label label = new Label(index++, 0, bean.getShowName());
                sheet.addCell(label);
            }

            // 实体属性与get方法Map
            Map<String, Method> fieldReadMethodMap = getFieldReadMethodMap(clazz);
            // 填充数据
            for (int i = 0; i < data.size(); i++) {
                index = 0;
                for (ExcelBean bean : excelBeanList) {
                    // 获取field名称
                    String property = bean.getProperty();
                    // 获取field对应的get方法
                    Method readMethod = fieldReadMethodMap.get(property);
                    if (readMethod == null) {
                        throw new RuntimeException("属性" + property + "在" + clazz.getName() + "类中没有getter方法");
                    }
                    readMethod.setAccessible(true);
                    // 获得field的值
                    Object value = readMethod.invoke(data.get(i), new Object[] {});
                    if (value == null) {// value为空,显示空串
                        Label label = new Label(index++, i + 1, "");
                        sheet.addCell(label);
                    } else {
                        // 创建Cell
                        if (value instanceof java.lang.Integer || value instanceof java.lang.Long) {// 数字类型
                            Label label = new Label(index++, i + 1, String.valueOf(value));
                            sheet.addCell(label);
                        } else if (value instanceof java.lang.Number) {// 数字类型(Decimal(15))
                            Number number = new Number(index++, i + 1, ((java.lang.Number) value).doubleValue());
                            sheet.addCell(number);
                        } else if (value instanceof Date) {// 日期类型
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                            String dateStr = sdf.format((Date) value);
                            Label label = new Label(index++, i + 1, dateStr);
                            sheet.addCell(label);
                        } else {// 其他
                            Label label = new Label(index++, i + 1, value.toString());
                            sheet.addCell(label);
                        }
                    }

                }
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            logger.error("", e);
        }
    }

    /**
     * excel导出（只支持excel2007及以上，后缀为xlsx）
     * 使用SXSSFWorkbook，支持大数据量导出
     * 文档见http://poi.apache.org/spreadsheet/how-to.html#sxssf
     *
     * @author lichao 2017年12月29日
     * @param clazz
     *            实体Class
     * @param data
     *            导出数据
     * @param excelBeanList
     *            实体属性名和中文名组装的ExcelBean List
     * @param os
     *            输出流
     */
    public static <T> void exportExcelWithPoi(Class<T> clazz, List<T> data, List<ExcelBean> excelBeanList, OutputStream os) {
        try {
            // 创建workbook
            // 关闭auto-flushing，每2000条手动刷新
            SXSSFWorkbook wb = new SXSSFWorkbook(-1);
            // 创建sheet
            SXSSFSheet sheet = wb.createSheet();
            // 填充表头
            int rowIndex = 0;
            int cellIndex = 0;
            SXSSFRow titleRow = sheet.createRow(rowIndex++);
            for (ExcelBean bean : excelBeanList) {
                SXSSFCell cell = titleRow.createCell(cellIndex++);
                cell.setCellValue(bean.getShowName());
            }

            // 实体属性与get方法Map
            Map<String, Method> fieldReadMethodMap = getFieldReadMethodMap(clazz);
            // 填充数据
            for (int rowNum = 0; rowNum < data.size(); rowNum++) {
                SXSSFRow dataRow = sheet.createRow(rowIndex++);
                cellIndex = 0;
                for (ExcelBean bean : excelBeanList) {
                    // 获取field名称
                    String property = bean.getProperty();
                    // 获取field对应的get方法
                    Method readMethod = fieldReadMethodMap.get(property);
                    if (readMethod == null) {
                        throw new RuntimeException("属性" + property + "在" + clazz.getName() + "类中没有getter方法");
                    }
                    readMethod.setAccessible(true);
                    // 获得field的值
                    Object value = readMethod.invoke(data.get(rowNum), new Object[] {});
                    createPoiCell(dataRow, value, cellIndex++);
                }

                // 定量刷新到磁盘，减少内存消耗
                if(rowNum % 2000 == 0){
                    sheet.flushRows();
                }
            }
            wb.write(os);
            wb.dispose();// clean temporary files backing this workbook on disk
        } catch (Exception e) {
            logger.error("", e);
        }
    }
    
    /**
     * 创建cell
     * @author lichao
     * @date 2017/12/29 15:05
     */
    private static void createPoiCell(Row row, Object value, int cellIndex){
        org.apache.poi.ss.usermodel.Cell cell = row.createCell(cellIndex);
        if (value == null) {// value为空,显示空串
            cell.setCellValue("");
        } else {
            // 创建Cell
            if (value instanceof java.lang.Integer || value instanceof java.lang.Long) {// 数字类型
                cell.setCellValue(String.valueOf(value));
            } else if (value instanceof java.lang.Number) {// 数字类型(Decimal(15))
                cell.setCellValue(((java.lang.Number) value).doubleValue());
            } else if (value instanceof Date) {// 日期类型
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String dateStr = sdf.format((Date) value);
                cell.setCellValue(dateStr);
            } else {// 其他
                cell.setCellValue(value.toString());
            }
        }
    }

    /**
     * 
     * @author lichao 2015年12月1日
     * @param clazz
     *            实体Class
     * @return field:getMethod Map
     * @throws IntrospectionException
     */
    private static <T> Map<String, Method> getFieldReadMethodMap(Class<T> clazz) throws IntrospectionException {
        Map<String, Method> fieldReadMethodMap = new HashMap<String, Method>();
        BeanInfo beanInfo = Introspector.getBeanInfo(clazz);
        PropertyDescriptor[] proDescrtptors = beanInfo.getPropertyDescriptors();
        if (proDescrtptors != null && proDescrtptors.length > 0) {
            for (PropertyDescriptor pd : proDescrtptors) {
                fieldReadMethodMap.put(pd.getName(), pd.getReadMethod());
            }
        }
        return fieldReadMethodMap;
    }
}